package com.bds.portal.service;

import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.nutz.dao.Cnd;
import org.nutz.dao.impl.NutDao;
import org.nutz.dao.sql.Criteria;
import org.springframework.stereotype.Component;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.bds.model.ExportModel;
import com.bds.model.Template;
import com.bds.model.Tresult;
import com.bds.portal.util.ExcelUtil;

@Component
public class ExportService {

	private static Logger logger = Logger.getLogger(ExportService.class);

	@Resource
	private NutDao mysqlDao;

	public HSSFWorkbook exportTmpExcel(Long user_id, String request_id) {
		try {
			Template t = mysqlDao.fetch(Template.class, Cnd.where("request_id", "=", request_id));
			if (t != null) {
				List<ExportModel> list = new ArrayList<ExportModel>();

				JSONArray array = this.getAllTmpData(user_id, request_id);

				JSONArray head = new JSONArray();
				String[] ts = t.getHead().split(",");
				String[] titles = t.getTitle().split(",");
				for (int i = 0; i < ts.length; i++) {
					String prop = ts[i];
					JSONObject h = new JSONObject();
					h.put("prop", prop);
					h.put("title", titles[i]);
					head.add(h);
				}

				if (array != null && array.size() > 0) {
					ExportModel exp = new ExportModel();

					exp.setList(array);
					exp.setHead(head);
					list.add(exp);
					//
				}

				HSSFWorkbook wb = ExcelUtil.exportExcel(list);
				return wb;
			}
		} catch (Exception e) {
			logger.info("", e);

		}
		return null;
	}

	public List<String[]> exportTmpCsv(Long user_id, String request_id) {
		List<String[]> array = new ArrayList<String[]>();
		try {
			Template t = mysqlDao.fetch(Template.class, Cnd.where("request_id", "=", request_id));
			String[] ts = t.getHead().split(",");
			String[] titles = t.getTitle().split(",");
			JSONArray list = this.getAllTmpData(user_id, request_id);
			array.add(titles);
			for (int i = 0; i < list.size(); i++) {
				JSONObject json = list.getJSONObject(i);
				String[] row = new String[ts.length];
				for (int j = 0; j < ts.length; j++) {
					row[j] = json.getString(ts[j]);
				}
				array.add(row);

			}

		} catch (Exception e) {
			logger.error("", e);
		}
		return array;

	}

	public JSONArray getAllTmpData(Long user_id, String request_id) {

		Criteria cri = Cnd.cri();
		cri.where().andEquals("user_id", user_id);
		cri.where().andEquals("request_id", request_id);
		List<Tresult> list = mysqlDao.query(Tresult.class, cri);
		JSONArray array = new JSONArray();

		for (Tresult t : list) {
			JSONObject obj = JSONObject.parseObject(t.getResult());
			// obj.put("status", t.getStatus());
			array.add(obj);
		}

		return array;

	}

	public JSONObject exportTmpJson(Long user_id, String request_id) {
		JSONObject json = new JSONObject();
		try {
			Template t = mysqlDao.fetch(Template.class, Cnd.where("request_id", "=", request_id));
			if (t != null) {
				JSONArray list = new JSONArray();

				JSONArray array = this.getAllTmpData(user_id, request_id);

				JSONArray head = new JSONArray();
				String[] ts = t.getHead().split(",");
				String[] titles = t.getTitle().split(",");
				for (int i = 0; i < ts.length; i++) {
					String prop = ts[i];
					JSONObject h = new JSONObject();
					h.put("prop", prop);
					h.put("title", titles[i]);
					head.add(h);
				}

				if (array != null && array.size() > 0) {
					JSONObject row = new JSONObject();
					row.put("page", 1);
					row.put("data", array);
					list.add(row);
				}

				json.put("head", head);
				json.put("list", list);

				return json;
			}
		} catch (Exception e) {
			logger.info("", e);

		}
		return null;
	}

	/**
	 * public void exportSql(Long user_id, String sql_id, String config_id,
	 * String sel_requestId) { ExportRecord exportRecord = new ExportRecord();
	 * try { exportRecord.setUser_id(user_id); exportRecord.setCreate_time(new
	 * Date()); exportRecord.setRequest_id(sel_requestId);
	 * exportRecord.setTable_id(Long.parseLong(config_id));
	 * exportRecord.setStatus(1); mysqlDao.insert(exportRecord);
	 * List<TemplateUrl> task_list = mysqlDao.query(TemplateUrl.class,
	 * Cnd.where("request_id", "=", sel_requestId)); DataConfig dataConfig =
	 * mysqlDao.fetch(DataConfig.class, Cnd.where("id", "=", sql_id));
	 * ExportTable t_table = mysqlDao.fetch(ExportTable.class, Cnd.where("id",
	 * "=", config_id)); List<TemplateDetail> templateDetail =
	 * mysqlDao.query(TemplateDetail.class, Cnd.where("request_id", "=",
	 * sel_requestId)); Connection con = null; if
	 * (dataConfig.getType().equals("mysql")) {
	 * Class.forName("com.mysql.jdbc.Driver"); con =
	 * DriverManager.getConnection(dataConfig.getUrl(),
	 * dataConfig.getUsername(), dataConfig.getPwd()); } else if
	 * (dataConfig.getType().equals("sqlserver")) {
	 * Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); con =
	 * DriverManager.getConnection(dataConfig.getUrl(),
	 * dataConfig.getUsername(), dataConfig.getPwd()); } else if
	 * (dataConfig.getType().equals("oracle")) {
	 * Class.forName("oracle.jdbc.driver.OracleDriver"); con =
	 * DriverManager.getConnection(dataConfig.getUrl(),
	 * dataConfig.getUsername(), dataConfig.getPwd()); } else if
	 * (dataConfig.getType().equals("postgresql")) {
	 * Class.forName("org.postgresql.Driver"); con =
	 * DriverManager.getConnection(dataConfig.getUrl(),
	 * dataConfig.getUsername(), dataConfig.getPwd()); } Statement statement =
	 * con.createStatement(); if (null != task_list) { for (int i = 0; i <
	 * task_list.size(); i++) { String task_arr = task_list.get(i).getResult();
	 * // 获取当前页码下的数据 org.json.JSONArray array = new
	 * org.json.JSONArray(task_arr); String sql_col = "("; String attr_name[] =
	 * new String[templateDetail.size()]; int num = 0; String sql_val = "("; if
	 * (null != array) { // 用迭代的方法获取当前页json属性名。 org.json.JSONObject json =
	 * array.getJSONObject(0); Iterator<String> iterator = json.keys(); while
	 * (iterator.hasNext()) { attr_name[num] = iterator.next(); num++; } for
	 * (int j = 0; j < array.length(); j++) { // 当前jsonObject
	 * 
	 * // 拼接insert语句 for (int k = 0; k < num; k++) { if (j != array.length() -
	 * 1) { if (k != num - 1) { if (j == 0) { sql_col += "" + attr_name[k] +
	 * ","; } try { sql_val += "'" + array.getJSONObject(j).get(attr_name[k]) +
	 * "',"; } catch (JSONException e) { sql_val += "'',"; }
	 * 
	 * } else { if (j == 0) { sql_col += "" + attr_name[k] + ")"; } try {
	 * sql_val += "'" + array.getJSONObject(j).get(attr_name[k]) + "'),("; }
	 * catch (JSONException e) { sql_val += "''),("; } } } else { if (k != num -
	 * 1) { if (j == 0) { sql_col += "" + attr_name[k] + ","; } try { sql_val +=
	 * "'" + array.getJSONObject(j).get(attr_name[k]) + "',"; } catch
	 * (JSONException e) { sql_val += "'',"; } } else { if (j == 0) { sql_col +=
	 * "" + attr_name[k] + ")"; } try { sql_val += "'" +
	 * array.getJSONObject(j).get(attr_name[k]) + "');"; } catch (JSONException
	 * e) { sql_val += "'');"; } } } } } // 不为null每一页插入一次数据 String sql_insert =
	 * "INSERT INTO " + t_table.getName() + sql_col + " VALUES" + sql_val;
	 * statement.execute(sql_insert); } } } mysqlDao.update(ExportRecord.class,
	 * Chain.make("status", 2).add("update_time", new Date()), Cnd.where("id",
	 * "=", exportRecord.getId())); } catch (Exception e) { logger.info("", e);
	 * mysqlDao.update(ExportRecord.class, Chain.make("status",
	 * 3).add("update_time", new Date()), Cnd.where("id", "=",
	 * exportRecord.getId())); } }
	 **/
}
